﻿using Download;
using Microsoft.Office.Interop.Excel;
using StockDataBase.DataOperation;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Windows;
using System.Windows.Controls;

namespace 股票数据
{
    public class ExcelOp
    {
        ProgressBar pb;
        ProgressBar pb1;
        public ExcelOp(ProgressBar pb,ProgressBar pb1)
        {
            this.pb = pb;
            this.pb1 = pb1;
        }
        FinanceDataOp fop = new FinanceDataOp();

        /// <summary>
        /// dic 是行数和 表名的映射
        /// </summary>
        /// <param name="stockId"></param>
        /// <param name="data"></param>
        /// <param name="dic"></param>
        void XlsHandle(string stockId, object[,] data,Dictionary<int,string> map)
        {
            int row = 0;
            int colum = 0;
            try
            {
                row = data.GetLength(0);
                colum = data.GetLength(1);
            }
            catch (Exception)
            {

                return;
            }

            string find_it = "";
            foreach(var item in map)
            {
                if(item.Key == row)
                {
                    find_it = item.Value;
                    break;
                }
            }
            if(find_it == "")
            {
                throw new Exception("格式不对");
            }
            int row_start = 1;

            try
            {
                while (true)
                {
                    if (data[row_start, 2] != null)
                    {
                        break;
                    }
                    row_start++;
                }
            }
            catch (Exception)
            {
                Console.WriteLine(stockId + "不是股票");
                return;
            }



            for (int i = 2; i < colum; i++)
            {
                string time = null;
                string unit = null;
                string cash_kind = null;
                try
                {
                    time = data[row_start, i].ToString();
                    unit = data[row - 1, i].ToString();
                    cash_kind = data[row, i].ToString();
                }
                catch (Exception)
                {
                    continue;
                }

                Dictionary<string, Int64> dic = new Dictionary<string, Int64>();
                for (int j = row_start+1; j <= row - 2; j++)
                {
                    int fangda = 0;
                    if (unit == "百万")
                    {
                        fangda = 1000 * 1000;
                    }
                    else if (unit == "千")
                    {
                        fangda = 1000;
                    }
                    else if (unit == "元")
                    {
                        fangda = 1;
                    }
                    else
                    {
                        throw new Exception("单位错误");
                    }
                    Int64 num = 0;
                    if (string.IsNullOrWhiteSpace(data[j, i] as string))
                    {
                        num = 0;
                    }
                    else
                    {
                        num = Int64.Parse(data[j, i].ToString().Replace(",", ""));

                    }
                    num = num * (Int64)fangda;
                    //判断币种
                    switch(cash_kind)
                    {
                        case "人民币" :
                        {
                            break;
                        }
                        case "港币":
                        {
                            num = (Int64)(num * 0.8347);
                            break;
                        }
                        case "美元":
                        {
                            num = (Int64)(num * 6.4730);
                            break;
                        }
                        case "加元" :
                        {
                            num = (Int64)(num * 5.0495);
                            break;
                        }
                        case "欧元":
                        {
                            num = (Int64)(num * 7.3321);
                            break;
                        }
                        case "英镑":
                        {
                            num = (Int64)(num * 9.2277);
                            break;
                        }
                        case "新加坡元":
                        {
                            num = (Int64)(num * 4.7839);
                            break;
                        }
                        case "日元":
                        {
                            num = (Int64)(num * 0.0595);
                            break;
                        }
                        default:
                            Console.WriteLine("币种不对" + cash_kind);
                            return;
                    }

                    dic.Add(data[j, 1].ToString(), num);
                }
                fop.InsertTable(stockId, time, dic, find_it);
            }
        }

        public void Clear退市()
        {
            pb.Minimum = 0;
            DirectoryInfo di = new DirectoryInfo(@"E:\lern\数据\data");
            var dis = di.GetDirectories();
            pb.Maximum = dis.Length;
            pb.Value = 0;
            foreach(var item in dis)
            {
                string id = "hk" + item.Name;
                string sql = @"select * from baseInfo where StockId='"+id+"'";
                var res = this.fop.ExcuteReader(sql);
                if (! res.Read())
                { 
                    //数据库里面找不到的统统删除
                    item.Delete(true);
                    Console.WriteLine(id);
                }
                pb.Value++;
            }
            MessageBox.Show("清除成功");

        }


        public void HandleAll(string tableKind)
        {
            int tblId = 0;
            switch(tableKind)
            {
                case "cash" :
                    {
                        tblId = 2;
                        break;
                    }
                case "asset":
                    {
                        tblId = 1;
                        break;
                    }
                case "profit":
                    {
                        tblId = 3;
                        break;
                    }
            }

            DirectoryInfo di = new DirectoryInfo(@"E:\lern\数据\data\");
            var dis = di.GetDirectories();
            pb.Minimum = 0;
            pb.Maximum = dis.Length;
            int idx = 0;
            new Thread(() =>
            {
                fop.Begin();

                foreach (var sdi in dis)
                {
                    this.OpenExcel(sdi.FullName + @"\"+tblId+".xls", tableKind, "hk" + sdi.Name);
                    pb.Dispatcher.BeginInvoke(new System.Action(() =>
                    {
                        pb.Value = idx++;
                    })
                    );
                }
                fop.End();

                pb.Dispatcher.BeginInvoke(new System.Action(() =>
                {
                    MessageBox.Show("转换完毕");
                }));

            }).Start();
        }




        object missing = System.Reflection.Missing.Value;
        Microsoft.Office.Interop.Excel.Application excel = null;

        //读取EXCEL的方法   (用范围区域读取数据)
        public void OpenExcel(string strFileName,string kind,string stockId)
        {
            //if(kind == "asset")
            //{
            //    var reader = this.fop.ExcuteReader("select StockId from asset where StockId='" + stockId + "'");
            //    if (reader.Read())
            //    {
            //        return; //找到了 说明曾经插进去了 就不查
            //    }
            //    reader = this.fop.ExcuteReader("select StockId from asset_bank where StockId='" + stockId + "'");
            //    if (reader.Read())
            //    {
            //        return; //找到了 说明曾经插进去了 就不查
            //    }
            //}
            //else if (kind == "profit")
            //{
            //    var reader = this.fop.ExcuteReader("select StockId from profit where StockId='" + stockId + "'");
            //    if (reader.Read())
            //    {
            //        return; //找到了 说明曾经插进去了 就不查
                    
            //    }
            //    reader = this.fop.ExcuteReader("select StockId from profit_bank where StockId='" + stockId + "'");
            //    if (reader.Read())
            //    {
            //        return; //找到了 说明曾经插进去了 就不查
            //    }
            //}

            if (excel == null)
            {
                excel = new Microsoft.Office.Interop.Excel.Application();//lauch excel application
                excel.Visible = false;
                excel.UserControl = true;
            }

        
            // 以只读的形式打开EXCEL文件
            Workbook wb = null;
            try
            {
                wb = excel.Application.Workbooks.Open(strFileName, missing, true, missing, missing, missing,
            missing, missing, missing, true, missing, missing, missing, missing, missing);
            }
            catch (Exception)
            {

                return;
            }
            //取得第一个工作薄
            Worksheet ws = (Worksheet)wb.Worksheets.get_Item(1);


            //取得总记录行数   (包括标题列)
            int row = ws.UsedRange.Cells.Rows.Count; //得到行数
            int colum = ws.UsedRange.Cells.Columns.Count;//得到列数

            Range from = ws.Cells[1, 1];
            Range to = ws.Cells[row, colum];

            //取得数据范围区域 (不包括标题列) 
            Range rng1 = ws.Cells.get_Range(from, to);   //item
            object[,] arryItem = (object[,])rng1.Value2;   //get range's value
            if (kind == "asset")
            {
                XlsHandle(stockId, arryItem, new Dictionary<int, string>() { 
                    {27,"asset"},
                    {31,"asset_bank"},
                });
            }
            else if (kind == "profit")
            {
                XlsHandle(stockId, arryItem, new Dictionary<int, string>() { 
                    {22,"profit"},
                    {21,"profit_bank"},
                });
            }
            else if (kind == "cash")
            {
                XlsHandle(stockId, arryItem, new Dictionary<int, string>() { 
                    {12,"cash"},
                });
            }
            wb.Close();
       
        }

    }
}
